﻿using System;
using System.Data;
using Model;
using Oracle.DataAccess.Client;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections.Generic;
using Common;
using Npgsql;

namespace DAL
{

    public class DB
    {
        HIDB.dbo DBCon = HIDB.common.make();
        public LoginInfo loginInfo
        {
            get;
            set;
        }

        public static DataTable DBSqlConn(string conn, string sql)
        {
            using (SqlConnection connection = new SqlConnection(conn))
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(sql, connection);
                    SqlDataAdapter myAdapter = new SqlDataAdapter(command);
                    DataTable table = new DataTable();
                    myAdapter.Fill(table);
                    return table;
                }
                catch (Exception E)
                {
                    throw E;
                }
            }
        }

        public static DataTable DBOracleConn(string conn, string sql)
        {
            using (Oracle.DataAccess.Client.OracleConnection connection = new Oracle.DataAccess.Client.OracleConnection(conn))
            {
                try
                {
                    DataSet ds = new DataSet();
                    connection.Open();
                    OracleDataAdapter command = new OracleDataAdapter(sql, connection);
                    command.Fill(ds, "ds");
                    if (ds != null && ds.Tables.Count > 0)
                        return ds.Tables[0];
                    else
                        return new DataTable();
                }
                catch (Oracle.DataAccess.Client.OracleException E)
                {
                    connection.Close();
                    throw new Exception(E.Message);
                }
            }
        }
        public static DataTable DBOracleConn(string conn, string sql, params IDataParameter[] cmdParms)
        {
            OracleConnection cn = new OracleConnection(conn);
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                OracleCommand cmd = new OracleCommand(sql, cn);
                OracleDataAdapter sda = new OracleDataAdapter();
                sda.SelectCommand = cmd;
                sda.SelectCommand.CommandTimeout = 60;

                cmd.CommandType = CommandType.Text;//cmdType;
                if (cmdParms != null)
                {
                    foreach (OracleParameter parameter in cmdParms)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(parameter);
                    }
                }

                sda.Fill(dt);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cn.Close();
            }
            return dt;
        }

        public static DataTable DBMySqlConn(string conn, string sql)
        {
            using (MySqlConnection connection = new MySqlConnection(conn))
            {
                try
                {
                    connection.Open();
                    MySqlCommand command = new MySqlCommand(sql, connection);
                    MySqlDataAdapter myAdapter = new MySqlDataAdapter(command);
                    DataTable table = new DataTable();
                    myAdapter.Fill(table);
                    return table;
                }
                catch (Exception E)
                {
                    throw E;
                }
            }
        }
        public static DataTable DBMySqlConn(string conn, string sql, params IDataParameter[] cmdParms)
        {
            MySqlConnection cn = new MySqlConnection(conn);
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                MySqlCommand cmd = new MySqlCommand(sql, cn);
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = cmd;
                sda.SelectCommand.CommandTimeout = 60;

                cmd.CommandType = CommandType.Text;//cmdType;
                if (cmdParms != null)
                {
                    foreach (MySqlParameter parameter in cmdParms)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(parameter);
                    }
                }

                sda.Fill(dt);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                if (dt.Rows.Count > 0)
                    return dt;
                else
                    throw ex;
            }
            finally
            {
                cn.Close();
            }
            return dt;
        }

        public static DataTable DBNpgsqlConn(string conn, string sql)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(conn))
            {
                try
                {
                    connection.Open();
                    NpgsqlCommand command = new NpgsqlCommand(sql, connection);
                    NpgsqlDataAdapter myAdapter = new NpgsqlDataAdapter(command);
                    DataTable table = new DataTable();
                    myAdapter.Fill(table);
                    return table;
                }
                catch (Exception E)
                {
                    throw E;
                }
            }
        }
        public static DataTable DBNpgsqlConn(string conn, string sql, params IDataParameter[] cmdParms)
        {
            NpgsqlConnection cn = new NpgsqlConnection(conn);
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                NpgsqlCommand cmd = new NpgsqlCommand(sql, cn);
                NpgsqlDataAdapter sda = new NpgsqlDataAdapter();
                sda.SelectCommand = cmd;
                sda.SelectCommand.CommandTimeout = 60;

                cmd.CommandType = CommandType.Text;//cmdType;
                if (cmdParms != null)
                {
                    foreach (NpgsqlParameter parameter in cmdParms)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(parameter);
                    }
                }

                sda.Fill(dt);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cn.Close();
            }
            return dt;
        }

        public static DataTable DBExcelConn(string conn, string sql)
        {
            string exlConnstr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES\";", conn);
            using (OleDbConnection connection = new OleDbConnection(exlConnstr))
            {
                try
                {
                    connection.Open();
                    OleDbCommand command = new OleDbCommand(sql, connection);
                    OleDbDataAdapter myAdapter = new OleDbDataAdapter(command);
                    DataTable table = new DataTable();
                    myAdapter.Fill(table);
                    return table;
                }
                catch (Exception E)
                {
                    throw E;
                }
            }
        }
        public HIDB.dbo ViewCon()
        {
            return conn();
        }
        public HIDB.dbo conn()
        {
            string connStr = DBCon.GetConnectString();
            string dataBase = ConfigurationManager.AppSettings["DataBase"].ToString();
            if (string.IsNullOrEmpty(loginInfo.DBName) || loginInfo.DBName == "null" || loginInfo.DBName == "undefined")
            {
                HIDB.dbo d = HIDB.common.make();
                d.SqlTimeOut = 21600;//6小时
                return d;
            }
            else
            {
                HIDB.dbo d = HIDB.common.make(HIDB.DBType.SQLServer, connStr.Replace(dataBase, loginInfo.DBName));
                d.SqlTimeOut = 21600;
                return d;
            }
        }
        public HIDB.dbo MSDB()
        {
            string connStr = DBCon.GetConnectString();
            string dataBase = ConfigurationManager.AppSettings["DataBase"].ToString();
            HIDB.dbo con = HIDB.common.make(HIDB.DBType.SQLServer, connStr.Replace(dataBase, "msdb"));
            return con;
        }
        public HIDB.dbo DBReadOnly()
        {
            string connStr = ConfigurationManager.ConnectionStrings["ConnectionReadOnly"].ToString();
            HIDB.dbo con = HIDB.common.make(HIDB.DBType.SQLServer, connStr);
            return con;
        }
        public int ExistDB(string dbName, bool isDisplay)
        {
            SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@DBName",dbName)
                };
            string sql = "select count(*) as count from  [Working_db].[DBDeploy] where DBName=@DBName";
            if (isDisplay)
                sql = "select count(*) as count from  [Working_db].[DBDeploy] where DisplayName=@DBName";
            DataTable dt = DBCon.GetDataTable(sql, para);
            int count = 0;
            if (dt != null && dt.Rows.Count > 0)
            {
                count = Convert.ToInt32(dt.Rows[0][0]);
            }
            return count;
        }
        public DBDeploy GetDBInfo(string dbName)
        {
            List<DBDeploy> dbList = new List<DBDeploy>();
            SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@DBName",dbName)
                };
            try
            {
                DataTable dt = DBCon.GetDataTable("select * from  [Working_db].[DBDeploy] where DBName=@DBName", para);
                dbList = dt.MapToList<DBDeploy>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dbList.Count > 0)
                return dbList[0];
            else
                return new DBDeploy();
        }
        public bool DropDataBase(string dbName)
        {
            SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@DBName",dbName)
                };
            try
            {
                DBCon.ExecuteSql(string.Format(@"
declare @jobname nvarchar(max)=@DBName+'_ETL'
declare @job_id nvarchar(max)
if exists (select 1 from msdb.dbo.sysjobs a where a.name=@jobname)
begin
	select @job_id=a.job_id from msdb.dbo.sysjobs a where a.name=@jobname

	EXEC msdb.dbo.sp_delete_job @job_id=@job_id, @delete_unused_schedule=1
end
;
ALTER DATABASE [{0}]	SET SINGLE_USER;
DROP DATABASE [{0}];
delete from [Working_db].[DBDeploy] where DBName=@DBName", dbName), para);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool RenameDB(string dbName, string newDBName)
        {
            SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@DBName",dbName),
                    new SqlParameter("@NewDBName",newDBName)
                };
            try
            {
                DBCon.ExecuteSql("update [Working_db].[DBDeploy] set DisplayName=@NewDBName where DBName=@DBName", para);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool UpDataBaseStatus(string dbStatus, string dbName)
        {
            SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@DBName",dbName),
                    new SqlParameter("@DBStatus",dbStatus)
                };
            try
            {
                DBCon.ExecuteSql("update [Working_db].[DBDeploy] set DBStatus=@DBStatus where DBName=@DBName", para);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool ConnectDB(string dbStatus, string userName, string dbName)
        {
            SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@DBName",dbName),
                    new SqlParameter("@UserName",userName)
                };
            try
            {
                if (dbStatus == "1")
                    DBCon.ExecuteSql("insert into [Working_db].[UserDBConnType] select NEWID(),@UserName,@DBName,GETDATE()", para);
                else
                    DBCon.ExecuteSql("delete from [Working_db].[UserDBConnType] where UserName=@UserName and DBName=@DBName", para);

                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool CreateDB(string dbName, string dbDisplayName, string LogicalName, string dbPath, string BackUpFilePath, string dbType)
        {
            try
            {
                SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@DBName",dbName),
                    new SqlParameter("@DisplayName",dbDisplayName),
                    new SqlParameter("@DBType",dbType)
                };
                DBCon.ExecuteSql(string.Format("backup database mdm_bak to disk= '{0}' with init", BackUpFilePath));
                if (DBCon.CreateDataBase(dbName, LogicalName, dbPath, BackUpFilePath))
                {
                    DBCon.ExecuteSql(@"insert into [Working_db].[DBDeploy](DBName,DisplayName,DBType,DBStatus,DBJobStatus) values(@DBName,@DisplayName,@DBType,1,1)
insert into [dbo].[SysRoleDBTable]
select '3b46ae04-0152-483a-8ec4-5fa35edfbff2',id,0,1,1,1 from Working_db.DBDeploy where DBName=@DBName", para);
                    return true;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static bool ModifyModel(string path)
        {
            try
            {
                HIDB.common.ApplyDtsx(path);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public bool CreateJob(string sql)
        {
            try
            {
                HIDB.dbo con = this.MSDB();

                con.ExecuteSql(sql);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}
